package com.doujiang.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.doujiang.domain.Movie;
import com.doujiang.domain.dto.jhjMovieDTO;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 电影Mapper接口
 * 
 * @author 王伟舰
 * @date 2025-07-19
 */
@Repository
@Mapper
public interface jhjMovieMapper extends BaseMapper<Movie>
{
    /**
     * 查询电影
     * 
     * @param id 电影主键
     * @return 电影
     */
    public Movie selectMovieById(Long id);

    /**
     * 查询电影列表
     * 
     * @param movie 电影
     * @return 电影集合
     */
    public List<Movie> selectMovieList(Movie movie);

    /**
     * 新增电影
     * 
     * @param movie 电影
     * @return 结果
     */
    public int insertMovie(Movie movie);

    /**
     * 修改电影
     * 
     * @param movie 电影
     * @return 结果
     */
    public int updateMovie(Movie movie);

    /**
     * 删除电影
     * 
     * @param id 电影主键
     * @return 结果
     */
    public int deleteMovieById(Long id);

    /**
     * 批量删除电影
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteMovieByIds(Long[] ids);


    @Select("SELECT ROUND(AVG(score), 2) FROM rating WHERE deleted = 0")
    Double selectGlobalAvgScore();

    @Select("""
        SELECT 
            m.id, 
            m.title, 
            m.director, 
            m.release_date AS releaseDate, 
            m.poster, 
            COUNT(s.id) AS voteCount, 
            ROUND(AVG(s.score), 2) AS avgScore, 
            ROUND(
                (COUNT(s.id) / (COUNT(s.id) + #{minVotes})) * AVG(s.score) + 
                (#{minVotes} / (COUNT(s.id) + #{minVotes})) * #{globalAvg}, 
                2
            ) AS finalScore
        FROM movie m
        JOIN rating s ON m.id = s.movie_id AND s.deleted = 0
        JOIN movie_tag mt ON m.id = mt.movie_id AND mt.deleted = 0
        JOIN tag t ON mt.tag_id = t.id AND t.deleted = 0
        WHERE m.deleted = 0
          AND EXISTS (
              SELECT 1 FROM movie_tag mt2
              JOIN tag t2 ON mt2.tag_id = t2.id
              WHERE mt2.movie_id = m.id AND mt2.deleted = 0 AND t2.name = '剧情'
          )
          AND EXISTS (
              SELECT 1 FROM movie_tag mt3
              JOIN tag t3 ON mt3.tag_id = t3.id
              WHERE mt3.movie_id = m.id AND mt3.deleted = 0 AND t3.name IN ('悬疑', '惊悚')
          )
        GROUP BY m.id
        HAVING voteCount >= #{minVotes}
        ORDER BY finalScore DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectTopSuspenseMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);


    @Select("""
        SELECT
            m.id,
            m.title,
            m.director,
            m.release_date,
            m.poster,
            COUNT(r.id) AS vote_count,
            ROUND(AVG(r.score), 2) AS avg_score,
            ROUND(
                (COUNT(r.id) / (COUNT(r.id) + #{minVotes})) * AVG(r.score) +
                (#{minVotes} / (COUNT(r.id) + #{minVotes})) * #{globalAvg},
                2
            ) AS final_score
        FROM
            movie m
            JOIN rating r ON m.id = r.movie_id AND r.deleted = 0
        WHERE
            m.deleted = 0
        GROUP BY
            m.id
        HAVING
            COUNT(r.id) >= #{minVotes} 
        ORDER BY
            final_score DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectHighScoreMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);


    @Select("""
        SELECT
            m.id,
            m.title,
            m.director,
            m.release_date,
            m.poster,
            COUNT(s.id) AS vote_count,
            ROUND(AVG(s.score), 2) AS avg_score,
            ROUND(
                (COUNT(s.id) / (COUNT(s.id) + #{minVotes})) * AVG(s.score) +
                (#{minVotes} / (COUNT(s.id) + #{minVotes})) * #{globalAvg},
                2
            ) AS final_score
        FROM
            movie m
            JOIN rating s ON m.id = s.movie_id AND s.deleted = 0
            JOIN movie_tag mt ON m.id = mt.movie_id AND mt.deleted = 0
            JOIN tag t ON mt.tag_id = t.id AND t.deleted = 0
        WHERE
            m.deleted = 0
            AND YEAR(m.release_date) BETWEEN 2020 AND 2026
        GROUP BY
            m.id
        HAVING
            vote_count >= #{minVotes}
        ORDER BY
            final_score DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectLatestMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);


    @Select("""
        SELECT
            m.id,
            m.title,
            m.director,
            m.release_date,
            m.poster,
            COUNT(s.id) AS vote_count,
            ROUND(AVG(s.score), 2) AS avg_score,
            ROUND(
                (COUNT(s.id) / (COUNT(s.id) + #{minVotes})) * AVG(s.score) +
                (#{minVotes} / (COUNT(s.id) + #{minVotes})) * #{globalAvg},
                2
            ) AS final_score
        FROM
            movie m
            JOIN rating s ON m.id = s.movie_id AND s.deleted = 0
            JOIN movie_tag mt ON m.id = mt.movie_id AND mt.deleted = 0
            JOIN tag t ON mt.tag_id = t.id AND t.deleted = 0
        WHERE
            m.deleted = 0
            AND EXISTS (
                SELECT 1 FROM movie_tag mt2
                JOIN tag t2 ON mt2.tag_id = t2.id
                WHERE mt2.movie_id = m.id AND mt2.deleted = 0 AND t2.name = '动作'
            )
            AND EXISTS (
                SELECT 1 FROM movie_tag mt3
                JOIN tag t3 ON mt3.tag_id = t3.id
                WHERE mt3.movie_id = m.id AND mt3.deleted = 0 AND t3.name IN ('犯罪', '惊悚')
            )
        GROUP BY
            m.id
        HAVING
            vote_count >= #{minVotes}
        ORDER BY
            final_score DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectActionThrillerMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);


    @Select("""
        SELECT
            m.id,
            m.title,
            m.director,
            m.release_date,
            m.poster,
            COUNT(s.id) AS vote_count,
            ROUND(AVG(s.score), 2) AS avg_score,
            ROUND(
                (COUNT(s.id) / (COUNT(s.id) + #{minVotes})) * AVG(s.score) +
                (#{minVotes} / (COUNT(s.id) + #{minVotes})) * #{globalAvg},
                2
            ) AS final_score
        FROM
            movie m
            JOIN rating s ON m.id = s.movie_id AND s.deleted = 0
            JOIN movie_tag mt ON m.id = mt.movie_id AND mt.deleted = 0
            JOIN tag t ON mt.tag_id = t.id AND t.deleted = 0
        WHERE
            m.deleted = 0
            AND EXISTS (
                SELECT 1 FROM movie_tag mt3
                JOIN tag t3 ON mt3.tag_id = t3.id
                WHERE mt3.movie_id = m.id AND mt3.deleted = 0 AND t3.name IN ('奇幻', '科幻')
            )
        GROUP BY
            m.id
        HAVING
            vote_count >= #{minVotes}
        ORDER BY
            final_score DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectFantasySciFiMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);


    @Select("""
        SELECT
            m.id,
            m.title,
            m.director,
            m.release_date,
            m.poster,
            COUNT(s.id) AS vote_count,
            ROUND(AVG(s.score), 2) AS avg_score,
            ROUND(
                (COUNT(s.id) / (COUNT(s.id) + #{minVotes})) * AVG(s.score) +
                (#{minVotes} / (COUNT(s.id) + #{minVotes})) * #{globalAvg},
                2
            ) AS final_score
        FROM
            movie m
            JOIN rating s ON m.id = s.movie_id AND s.deleted = 0
        WHERE
            m.deleted = 0
            AND m.director = '克里斯托弗·诺兰'
        GROUP BY
            m.id
        HAVING
            vote_count >= #{minVotes}
        ORDER BY
            final_score DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectNolanMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);


    @Select("""
        SELECT
            m.id,
            m.title,
            m.director,
            m.release_date,
            m.poster,
            COUNT(s.id) AS vote_count,
            ROUND(AVG(s.score), 2) AS avg_score,
            ROUND(
                (COUNT(s.id) / (COUNT(s.id) + #{minVotes})) * AVG(s.score) +
                (#{minVotes} / (COUNT(s.id) + #{minVotes})) * #{globalAvg},
                2
            ) AS final_score
        FROM
            movie m
            JOIN rating s ON m.id = s.movie_id AND s.deleted = 0
            JOIN movie_tag mt ON m.id = mt.movie_id AND mt.deleted = 0
            JOIN tag t ON mt.tag_id = t.id AND t.deleted = 0
        WHERE
            m.deleted = 0
            AND EXISTS (
                SELECT 1 FROM movie_tag mt3
                JOIN tag t3 ON mt3.tag_id = t3.id
                WHERE mt3.movie_id = m.id AND mt3.deleted = 0 AND t3.name ='华语'
            )
        GROUP BY
            m.id
        HAVING
            vote_count >= #{minVotes}
        ORDER BY
            final_score DESC
        LIMIT 5
    """)
    List<jhjMovieDTO> selectChineseMovies(@Param("minVotes") int minVotes, @Param("globalAvg") double globalAvg);

    List<jhjMovieDTO> selectMoreByType(@Param("name") String name,
                                       @Param("minVotes") int minVotes,
                                       @Param("globalAvg") double globalAvg,
                                       @Param("offset") int offset,
                                       @Param("size") int size);

    int countByType(@Param("name") String name, @Param("minVotes") int minVotes);


}
